﻿CREATE PROCEDURE reports.s_Notes_Ugly AS
DECLARE @DateFrom datetime SET @DateFrom = dbo.Today(DATEADD(mm,-1,GETDATE()))
DECLARE @DateTo datetime SET @DateTo = DATEADD(dd,14,@DateFrom)
DECLARE @ADS_COLLECTORS TABLE (sAMAccountName sysname,ContainerName sysname)

INSERT INTO @ADS_COLLECTORS
SELECT DISTINCT   sAMAccountName,ContainerName
FROM v_ADS_Collector

DECLARE @NOTES TABLE(ID int,LoanID int,ActionCodeID int,StatusID int,CompanyID_Raw int,NoteUser varchar(32))
INSERT INTO @NOTES
SELECT NOTE.ID,NOTE.LoanID,NOTE.NoteActionCodeID,NOTE.StatusID,CF_E.CompanyID_Raw,NoteUser
FROM (
SELECT ID,LoanID,NoteUser,StatusID,NoteActionCodeID
FROM t_Note WITH(nolock) WHERE NoteTypeID = 1 AND NoteDate0 BETWEEN '1/1/2009' AND '1/31/2009'
) AS NOTE
INNER JOIN usix.t_CustomerFile_Export CF_E ON NOTE.LoanID = CF_E.LoanID

DECLARE @STATES TABLE (ID int,Name varchar(32),IsGood bit)
INSERT INTO @STATES
SELECT ID,Name
,CONVERT(int,
COALESCE(
dbo.fs_StateParamValue(S.ID,dbo.fs_StateParamID__IsPayments()),
dbo.fs_StateParamValue(S.ID,dbo.fs_StateParamID__IsPTP()),
dbo.fs_StateParamValue(S.ID,dbo.fs_StateParamID__IsContact()),
0)
)IsGood
FROM t_State S

SELECT COUNT(*)FROM
(SELECT LoanID FROM @NOTES N GROUP BY LoanID)T

SELECT NOTES.LoanID,NOTES.Count FROM
(SELECT DISTINCT N.LoanID
 FROM @NOTES N INNER JOIN @STATES S ON N.StatusID = S.ID WHERE S.IsGood = 1 OR N.ActionCodeID = dbo.enum__NoteActionCode__CustomerCalled()
)GOOD RIGHT OUTER JOIN (SELECT LoanID,COUNT(*) Count FROM @NOTES GROUP BY LoanID HAVING Count(*) > 5) NOTES
ON NOTES.LoanID = GOOD.LoanID
WHERE GOOD.LoanID IS NULL
